DECLARE @xml XML

SET @xml = 
    (
        SELECT
            SegmentId       = ISNULL(SegmentId,0)
        ,   SegmentName     = CAST('<![CDATA[' + ISNULL(SegmentName,'') + ']]>' AS xml)
        ,   SortOrdinal     = ISNULL(CAST(SortOrdinal AS varchar(16)),'')
        ,   CreateDate      = ISNULL(Core.dbo.fDateFormat(@GlobalDateFormat,CreateDate),'')
        ,   ParentSegmentId = ISNULL(CAST(ParentSegmentId AS varchar(16)),'')
        ,   ClientId        = ISNULL(ClientId,'')
        ,   IsActive        = ISNULL(IsActive,0)
        FROM
            Segment
        WHERE
            dbo.fSegmentIsChildOf(SegmentId, dbo.fGetSegmentName(@SegmentId)) = 1
        FOR XML AUTO, ELEMENTS, ROOT('ChildSegments')
  )

SELECT @xml
